#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @FileName  :weather_data.py
# @Time      :2024/8/28 15:51
# @Author    :Raink
import datetime
import time

from sqlalchemy import create_engine
import pandas as pd
import requests
from pprint import pprint
from settings import key,sql_conn
def get_data(localtion):
    url="https://api.seniverse.com/v3/weather/daily.json?"
    params={
        "key":key,
        "location":localtion,
        "language":"zh-Hans",
        "unit":"c",
        "start":-1,
        "days":5
    }
    res=requests.get(url,params=params)
    return res
def parse_and_save_data(res):
    daily=pd.DataFrame(res.json()["results"][0]["daily"])
    location=pd.DataFrame(res.json()["results"][0]["location"],index=[0])
    concat_df=pd.concat([daily,location],axis=1)
    concat_df.ffill(inplace=True)
    concat_df["last_update"]=res.json()["results"][0]["last_update"]
    concat_df["update_time"]=datetime.datetime.now()
    concat_df["update_date"]=concat_df["update_time"].map(lambda x:x.date())
    return concat_df
    # data.to_excel("weather.xlsx")
def upload_to_database(df):
    df.to_sql("weather_3_days",sql_conn,if_exists="append",index=False)
def run():
    city_df=pd.read_excel("./城市列表.xlsx")
    for index,row in city_df.iterrows():
        try:
            res = get_data(row["城市ID"])
            print(index+1,"正在读取该行政区的数据:",row["行政归属"])
            param_data = parse_and_save_data(res)
            # print(param_data)
            upload_to_database(param_data)
            if (index + 1) % 20 == 0:
                time.sleep(61)
        except Exception as e:
            print(e)
            print("报错的行政区",row["城市ID"],row["行政归属"])

    print("done")

#print(param_data)